{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9b1528b3",
   "metadata": {},
   "source": [
    "# 数据统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "20d98308",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a93c948",
   "metadata": {},
   "source": [
    "## map、apply、applymap、transform\n",
    "\n",
    "- 对Series进行逐元素，DataFrame进行逐行、逐列或逐元素的操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02d23f1c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>econ_major</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>171</td>\n",
       "      <td>83</td>\n",
       "      <td>False</td>\n",
       "      <td>female</td>\n",
       "      <td>18</td>\n",
       "      <td>sophomore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>189</td>\n",
       "      <td>65</td>\n",
       "      <td>True</td>\n",
       "      <td>female</td>\n",
       "      <td>18</td>\n",
       "      <td>sophomore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>165</td>\n",
       "      <td>55</td>\n",
       "      <td>True</td>\n",
       "      <td>female</td>\n",
       "      <td>21</td>\n",
       "      <td>senior</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>150</td>\n",
       "      <td>80</td>\n",
       "      <td>False</td>\n",
       "      <td>male</td>\n",
       "      <td>18</td>\n",
       "      <td>freshman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>165</td>\n",
       "      <td>40</td>\n",
       "      <td>True</td>\n",
       "      <td>female</td>\n",
       "      <td>20</td>\n",
       "      <td>freshman</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  height  weight  econ_major  gender  age      grade\n",
       "0   1     171      83       False  female   18  sophomore\n",
       "1   2     189      65        True  female   18  sophomore\n",
       "2   3     165      55        True  female   21     senior\n",
       "3   4     150      80       False    male   18   freshman\n",
       "4   5     165      40        True  female   20   freshman"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "boolean=[True,False]\n",
    "gender=['female', 'male']\n",
    "grade = ['freshman', 'sophomore' , 'junior' , 'senior']\n",
    "df =pd.DataFrame({\n",
    "    'id' : np.arange(1,101,1),\n",
    "    'height' : np.random.randint(150,190,100),\n",
    "    'weight' : np.random.randint(40,90,100),\n",
    "    'econ_major' : np.random.choice(boolean, 100),\n",
    "    'gender' : np.random.choice(gender, 100),\n",
    "    'age' : np.random.randint(17,23,100),\n",
    "    \"grade\": np.random.choice(grade, 100)\n",
    "}\n",
    ")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fe5733a",
   "metadata": {},
   "source": [
    "### Series 处理\n",
    "\n",
    "- map()： 映射，进行数据转换\n",
    "- apply()： 调用自定义函数对数据进行处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3b46e31",
   "metadata": {},
   "outputs": [],
   "source": [
    "# map 对 Series内元素做映射\n",
    "df2 = df.copy(deep = True)\n",
    "# 使用字典进行映射\n",
    "# df2['gender'] = df2['gender'].map({'male': 0, 'female': 1})\n",
    "# print(df2)\n",
    "\n",
    "# 使用函数\n",
    "# NOTE: map的函数只能接收一个参数\n",
    "df2['gender'] = df2['gender'].map(lambda x: 0 if x == 'male' else 1)\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88535169",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>econ_major</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>grade</th>\n",
       "      <th>age2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>171</td>\n",
       "      <td>83</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>18</td>\n",
       "      <td>sophomore</td>\n",
       "      <td>18.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>189</td>\n",
       "      <td>65</td>\n",
       "      <td>True</td>\n",
       "      <td>2.0</td>\n",
       "      <td>18</td>\n",
       "      <td>sophomore</td>\n",
       "      <td>18.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>165</td>\n",
       "      <td>55</td>\n",
       "      <td>True</td>\n",
       "      <td>2.0</td>\n",
       "      <td>21</td>\n",
       "      <td>senior</td>\n",
       "      <td>21.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>150</td>\n",
       "      <td>80</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18</td>\n",
       "      <td>freshman</td>\n",
       "      <td>18.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>165</td>\n",
       "      <td>40</td>\n",
       "      <td>True</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20</td>\n",
       "      <td>freshman</td>\n",
       "      <td>20.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  height  weight  econ_major  gender  age      grade  age2\n",
       "0   1     171      83       False     2.0   18  sophomore  18.5\n",
       "1   2     189      65        True     2.0   18  sophomore  18.5\n",
       "2   3     165      55        True     2.0   21     senior  21.5\n",
       "3   4     150      80       False     1.0   18   freshman  18.5\n",
       "4   5     165      40        True     2.0   20   freshman  20.5"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# apply方法，传入函数\n",
    "# 作用原理和map方法类似，区别在于apply能够传入功能更为复杂的函数\n",
    "\n",
    "# df2 = df.copy()\n",
    "# df2['gender'] = df2['gender'].apply(lambda x: 0 if x == 'male' else 1)\n",
    "# # print(df2.head())\n",
    "\n",
    "def age_adj(x, adj):\n",
    "    return x+adj\n",
    "\n",
    "# apply()  可以传入多个参数\n",
    "df2['age2'] = df2['age'].apply(age_adj, args = (0.5,))\n",
    "df2.head()\n",
    "\n",
    "# apply()中传参数的方法\n",
    "# df.groupby().apply(ftn, args=(var,))\n",
    "# df.apply(ftn, **{'x':1, 'y':'女'})\n",
    "# df.apply(ftn, x = 1, y ='女')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91f928f7",
   "metadata": {},
   "source": [
    "### DataFrame 处理\n",
    "\n",
    "- `apply()`\n",
    "- `applymap()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "149d557a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     28.384802\n",
       "1     18.196579\n",
       "2     20.202020\n",
       "3     35.555556\n",
       "4     14.692378\n",
       "        ...    \n",
       "95    16.000000\n",
       "96    21.504470\n",
       "97    28.405338\n",
       "98    22.096808\n",
       "99    21.847009\n",
       "Length: 100, dtype: float64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# apply()\n",
    "# NOTE：需要确定是传0轴还是1轴\n",
    "df2 = df.copy()\n",
    "\n",
    "# def ftn(series): #传入的是一个Series\n",
    "#     # print(x)\n",
    "#     # print('-'*10)\n",
    "#     return x.height + x.weight\n",
    "# df2.apply(ftn, axis = 1)\n",
    "# df2['new_col'] = df2.apply(ftn, axis = 1)\n",
    "# df2.head()\n",
    "\n",
    "# def BMI(series):\n",
    "#     weight = series['weight']\n",
    "#     height = series['height']/100\n",
    "#     BMI = weight/height**2\n",
    "#     return BMI\n",
    "\n",
    "# df2.apply(BMI,axis =1)\n",
    "\n",
    "print(df2[['weight','height']].apply('sum', axis = 0)) # 返回一个Series\n",
    "print(df2[['weight','height']].apply(np.log, axis = 0))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a06f0bdb",
   "metadata": {},
   "source": [
    "- `apply()`说明\n",
    "  - axis=0，对每列columns执行指定函数；axis=1，对每行row执行指定函数。\n",
    "  - 无论axis=0还是axis=1，其传入指定函数的默认形式均为Series，可以通过设置raw=True传入numpy数组。\n",
    "  - 对每个Series执行结果后，会将结果整合在一起返回（若想有返回值，定义函数时需要return相应的值）\n",
    "  - `apply()`函数可以接收更复杂的函数，i.e. `df.apply(ftn, args = (0.5,), axis = 1)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "bc3b50f3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25.85%</td>\n",
       "      <td>2.26%</td>\n",
       "      <td>38.21%</td>\n",
       "      <td>24.91%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>94.65%</td>\n",
       "      <td>9.33%</td>\n",
       "      <td>59.47%</td>\n",
       "      <td>53.55%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11.33%</td>\n",
       "      <td>36.58%</td>\n",
       "      <td>24.32%</td>\n",
       "      <td>3.70%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91.63%</td>\n",
       "      <td>36.00%</td>\n",
       "      <td>59.58%</td>\n",
       "      <td>10.23%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6.09%</td>\n",
       "      <td>7.19%</td>\n",
       "      <td>84.34%</td>\n",
       "      <td>38.89%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20.55%</td>\n",
       "      <td>72.05%</td>\n",
       "      <td>9.67%</td>\n",
       "      <td>11.39%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>58.66%</td>\n",
       "      <td>22.70%</td>\n",
       "      <td>62.53%</td>\n",
       "      <td>81.23%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>34.08%</td>\n",
       "      <td>95.48%</td>\n",
       "      <td>25.14%</td>\n",
       "      <td>9.87%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>53.17%</td>\n",
       "      <td>79.35%</td>\n",
       "      <td>25.10%</td>\n",
       "      <td>1.65%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>73.27%</td>\n",
       "      <td>49.12%</td>\n",
       "      <td>37.11%</td>\n",
       "      <td>56.92%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        A       B       C       D\n",
       "0  25.85%   2.26%  38.21%  24.91%\n",
       "1  94.65%   9.33%  59.47%  53.55%\n",
       "2  11.33%  36.58%  24.32%   3.70%\n",
       "3  91.63%  36.00%  59.58%  10.23%\n",
       "4   6.09%   7.19%  84.34%  38.89%\n",
       "5  20.55%  72.05%   9.67%  11.39%\n",
       "6  58.66%  22.70%  62.53%  81.23%\n",
       "7  34.08%  95.48%  25.14%   9.87%\n",
       "8  53.17%  79.35%  25.10%   1.65%\n",
       "9  73.27%  49.12%  37.11%  56.92%"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# applymap(): 对DataFrame中的每个单元格执行指定函数的操作\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"A\":np.random.random(10),\n",
    "        \"B\":np.random.random(10),\n",
    "        \"C\":np.random.random(10),\n",
    "        \"D\":np.random.random(10)\n",
    "    }\n",
    ")\n",
    "df.head()\n",
    "# df.applymap(lambda x: round(x,2))\n",
    "df.applymap(lambda x: '{:.2%}'.format(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c7c56f0",
   "metadata": {},
   "source": [
    "## 分组\n",
    "\n",
    "- 使用groupby方法进行分组计算，得到分组对象GroupBy\n",
    "- `df.groupby(by=)`\n",
    "- 分组对象GroupBy可以运用描述性统计方法, 如count, mean, median, max和min等\n",
    "- NOTE： groupby的过程就是将原有的DataFrame按照groupby的字段，划分为若干个分组DataFrame，被分为多少个组就有多少个分组DataFrame。所以说，在groupby之后的一系列操作（如agg、apply等），均是基于子DataFrame的操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "2769de16",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3    100.000000\n",
      "4     89.231640\n",
      "5     96.412130\n",
      "6     89.514724\n",
      "7     93.104459\n",
      "Name: score_micro, dtype: float64\n",
      "        count       mean        std        min        25%        50%  \\\n",
      "gender                                                                    \n",
      "female   50.0  82.531772  12.753860  50.410851  75.123427  83.564792   \n",
      "male     50.0  85.715929  13.512473  40.638516  76.061492  89.772946   \n",
      "\n",
      "              75%    max  \n",
      "gender                       \n",
      "female  94.083821  100.0  \n",
      "male    97.980622  100.0  \n"
     ]
    }
   ],
   "source": [
    "np.random.seed(2022)\n",
    "\n",
    "# 创建一个df\n",
    "id = np.arange(1,101,1)\n",
    "score_micro = 15*np.random.randn(100) + 85\n",
    "gender = np.random.choice(['female','male'], 100)\n",
    "score_macro = 20*np.random.randn(100)+80\n",
    "\n",
    "df=pd.DataFrame({'id': id, 'score_micro': score_micro, 'score_macro': score_macro, 'gender': gender})\n",
    "\n",
    "df.head()\n",
    "df.describe()\n",
    "\n",
    "# 需要调整一下高于100的成绩\n",
    "def score_adj(score):\n",
    "    return score.map(lambda x: x if x<=100 else 100)\n",
    "df['score_micro'] = score_adj(df['score_micro'])\n",
    "df['score_macro'] = score_adj(df['score_micro'])\n",
    "# print(df.head())\n",
    "\n",
    "# 根据gender这一列进行分组\n",
    "group_by_gender = df.groupby('gender')\n",
    "# print(type(group_by_gender))\n",
    "\n",
    "# #查看分组\n",
    "# print(group_by_gender.groups)\n",
    "# #分组后的数量\n",
    "# print(group_by_gender.count())\n",
    "# #产看分组的情况\n",
    "# for name,group in group_by_gender:\n",
    "#     print(name)#组的名字\n",
    "#     print(group.head())#组具体内容\n",
    "\n",
    "#可以选择分组\n",
    "# print(group_by_gender.get_group('female').head())\n",
    "\n",
    "# 按照某一列进行分组，将score_micro这一列作为分组的键，对gender进行分组\n",
    "group_by_gender=df['score_micro'].groupby(df['gender'])\n",
    "# print(group_by_gender.count())\n",
    "print(group_by_gender.get_group('male').head())\n",
    "\n",
    "# 分组获取描述性信息，适用于数值型数据\n",
    "print(group_by_gender.describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95a82529",
   "metadata": {},
   "outputs": [],
   "source": [
    "#按照多列进行分组\n",
    "np.random.seed(2022)\n",
    "\n",
    "# 创建一个df\n",
    "id = list(range(1,26))*2\n",
    "score_micro = 15*np.random.randn(50) + 85\n",
    "gender = np.random.choice(['female','male'], 50)\n",
    "score_macro = 20*np.random.randn(50)+80\n",
    "year = [2021]*25 + [2022]*25\n",
    "\n",
    "df=pd.DataFrame({'id': id, 'year': year,'score_micro': score_micro, 'score_macro': score_macro, 'gender': gender})\n",
    "# print(df.head())\n",
    "\n",
    "# 需要调整一下高于100的成绩\n",
    "def score_adj(score):\n",
    "    return score.map(lambda x: x if x<=100 else 100)\n",
    "df['score_micro'] = score_adj(df['score_micro'])\n",
    "df['score_macro'] = score_adj(df['score_micro'])\n",
    "\n",
    "# 按性别、id分组\n",
    "group_by_gender_year = df.groupby(['gender','year'])\n",
    "\n",
    "# for name, group in group_by_gender_year:\n",
    "#     print(name)#组的名字\n",
    "#     print(group)#组具体内容\n",
    "\n",
    "# 可以选择分组\n",
    "print(group_by_gender_year.get_group(('male',2022)))\n",
    "# list(group_by_gender_year)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c35b722",
   "metadata": {},
   "source": [
    "### pd.cut() 将数值分段再进行分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "e5c12277",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     (65, 100]\n",
      "1      (40, 65]\n",
      "2      (40, 65]\n",
      "3      (40, 65]\n",
      "4      (40, 65]\n",
      "        ...    \n",
      "95     (40, 65]\n",
      "96     (40, 65]\n",
      "97     (19, 40]\n",
      "98     (19, 40]\n",
      "99     (19, 40]\n",
      "Name: Age, Length: 100, dtype: category\n",
      "Categories (3, interval[int64, right]): [(19, 40] < (40, 65] < (65, 100]]\n",
      "           Age  gender\n",
      "Age                \n",
      "(19, 40]    44   44\n",
      "(40, 65]    46   46\n",
      "(65, 100]   10   10\n"
     ]
    }
   ],
   "source": [
    "#将某列数据按数据值分成不同范围段进行分组(groupby)运算\n",
    "df = pd.DataFrame(\n",
    "    {'Age':np.random.randint(20,70,100),\n",
    "    'gender':np.random.choice(['M','F'],100)}\n",
    "    )\n",
    "age_groups =  pd.cut(df['Age'],bins=[19,40,65,100])  #左开右闭\n",
    "print(age_groups)\n",
    "\n",
    "print(df.groupby(age_groups).count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "541375f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# pd.qcut() 按分位数分段\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d5c1d6d",
   "metadata": {},
   "source": [
    "### 拓展"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "b826745d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               a         b         c         d         e\n",
      "Joe    -0.723142 -0.599503 -1.307187 -0.457507 -0.060073\n",
      "Steve  -0.646976 -0.996271 -1.839789 -0.754704  0.201384\n",
      "Wes    -0.984657       NaN       NaN -1.418455  0.700996\n",
      "Jim    -0.627791  0.696727 -1.453645 -0.929211 -0.881137\n",
      "Travis  1.312148  0.101942 -0.056825 -0.095257  0.910661\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>blue</th>\n",
       "      <th>red</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Joe</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Steve</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wes</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jim</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Travis</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        blue  red\n",
       "Joe        2    3\n",
       "Steve      2    3\n",
       "Wes        1    2\n",
       "Jim        2    3\n",
       "Travis     2    3"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 按字典or序列分组\n",
    "people = pd.DataFrame(np.random.randn(5, 5),\n",
    "    columns=['a', 'b', 'c', 'd', 'e'],\n",
    "    index=['Joe', 'Steve', 'Wes', 'Jim', 'Travis'])\n",
    "\n",
    "people.iloc[2:3, [1, 2]] = np.nan \n",
    "print(people)\n",
    "\n",
    "mapping = {\n",
    "    'a':'red', 'b':'red', 'c':'blue',\n",
    "    'd':'blue', 'e':'red', 'f':'orange'\n",
    "}\n",
    "\n",
    "# by_column = people.groupby(mapping, axis=1)\n",
    "\n",
    "# by_column.sum()\n",
    "\n",
    "# 或者使用Series分组\n",
    "map_series = pd.Series(mapping)\n",
    "people.groupby(map_series, axis=1).count()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "0665a83a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>cty</th>\n",
       "      <th>JP</th>\n",
       "      <th>US</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "cty  JP  US\n",
       "0     2   3\n",
       "1     2   3\n",
       "2     2   3\n",
       "3     2   3"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 按索引层次分组\n",
    "# \"多层索引\"\n",
    "columns = pd.MultiIndex.from_arrays([['US', 'US', 'US', 'JP', 'JP'],\n",
    "    [1, 3, 5, 1, 3]],\n",
    "    names=['cty', 'tenor'])\n",
    "\n",
    "\n",
    "hier_df = pd.DataFrame(np.random.randn(4, 5), columns=columns)\n",
    "\n",
    "hier_df.head()\n",
    "\n",
    "# To group by level, pass the level number or name using the level keyword:\n",
    "hier_df.groupby(level='cty', axis=1).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5884999",
   "metadata": {},
   "source": [
    "## 聚合函数\n",
    "\n",
    "- **Aggregations** refer to any data transformation that produces scalar values from arrays(输入是数组, 输出是标量值)\n",
    "- 可以对df使用聚合函数，或者结合`groupby()`使用\n",
    "\n",
    "  - mean: 计算平均值\n",
    "  - count: 分组中非NA值的数量\n",
    "  - size：计算分组后个数，与count函数的区别在于，size函数会计算NAN值，而count函数不会计算NAN值\n",
    "  - sum: 非NA值的和\n",
    "  - median: 非NA值的算术中位数\n",
    "  - std: 标准差\n",
    "  - var: 方差\n",
    "  - min: 非NA值的最小值\n",
    "  - max: 非NA值的最大值\n",
    "  - prod: 非NA值的积\n",
    "  - first: 第一个非NA值\n",
    "  - last: 最后一个非NA值\n",
    "  - mad: 平均绝对偏差\n",
    "  - mode: 模\n",
    "  - abs: 绝对值\n",
    "  - **sem： 平均值的标准误差**\n",
    "  - skew：样品偏斜度（三阶矩）\n",
    "  - kurt：样品峰度（四阶矩）\n",
    "  - quantile：样本分位数（百分位上的值）\n",
    "  - cumsum: 累积总和\n",
    "  - cumprod: 累积乘积\n",
    "  - cummax: 累积最大值\n",
    "  - cummin: 累积最小值\n",
    "  - nunique()   去掉重复值后进行计数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "3b0bfc7e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   col1  col2 key1 key2\n",
      "0    18    42    a    y\n",
      "1    10    46    b    y\n",
      "2    12    23    b    y\n",
      "3    16    25    a    y\n",
      "4    15    37    b    y\n",
      "col1    8\n",
      "col2    8\n",
      "key1    2\n",
      "key2    2\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame(\n",
    "    {'col1': np.random.randint(10,20,10),\n",
    "    'col2' : np.random.randint(20,50,10),\n",
    "    'key1' : np.random.choice(['a','b'], 10, p = [0.3, 0.7]),\n",
    "    'key2' : np.random.choice(['x','y'], 10)}\n",
    ")\n",
    "print(df.head())\n",
    "\n",
    "print(df.mean())  # 不是数值类的列（即麻烦列）会被清除\n",
    "\n",
    "# 按key1分组，进行聚合计算\n",
    "# 注意：当分组后进行数值计算时，不是数值类的列（即麻烦列）会被清除\n",
    "# print(df.groupby('key1').sum())\n",
    "# # 只计算col1\n",
    "# print(df['col1'].groupby(df['key1']).sum())\n",
    "# # 或者\n",
    "# print(df.groupby('key1')['col1'].sum())\n",
    "\n",
    "# print(df.groupby('key1')['col1'].quantile([0.25,0.75]))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0485f2af",
   "metadata": {},
   "source": [
    "### `agg()`\n",
    "\n",
    "- 同时做多个聚合运算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "e34d53bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "key2\n",
       "x    13.666667\n",
       "y    10.000000\n",
       "Name: mean, dtype: float64"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(\n",
    "    {'col1': np.random.randint(10,20,10),\n",
    "    'col2' : np.random.randint(20,50,10),\n",
    "    'key1' : np.random.choice(['a','b'], 10, p = [0.3, 0.7]),\n",
    "    'key2' : np.random.choice(['x','y'], 10)}\n",
    ")\n",
    "# print(df.head())\n",
    "\n",
    "# print(df.agg(['sum','mean']))\n",
    "\n",
    "# print(df.groupby('key1').agg(['sum','mean','std', np.std])) \n",
    "# # NOTE: 默认列名为函数名\n",
    "# # lambda functions have the name `<lambda` which makes them hard to identify\n",
    "# # 修改列名，即给分组字段取别名，通过元组提供：(name, ftn), 元组中的第一个变量为列名\n",
    "# print(df.groupby('key1').agg([('hey','sum'),'mean','std', ('bar', np.std)])) \n",
    "\n",
    "# #可自定义函数，传入agg方法中grouped.agg(func)\n",
    "# def peak_range(df):\n",
    "# #返回数值范围\n",
    "#     return df.max()-df.min()\n",
    "\n",
    "# print(df[['col1', 'col2']].groupby(df['key1']).agg(peak_range))\n",
    "\n",
    "# # 同时应用多个聚合函数\n",
    "# # print(df.groupby('key1').agg(['mean','std','count',peak_range]))\n",
    "\n",
    "# # 修改结果的列名（原为函数名），通过元组提供\n",
    "# # print(df.groupby('key1').agg(['mean','std','count',('range',peak_range)]))\n",
    "\n",
    "# # 给每列作用不同的聚合函数\n",
    "dict_mapping = {\n",
    "    'col1': ['mean', 'max'],\n",
    "    'col2': ['sum', peak_range],\n",
    "    'key1' : ['first']\n",
    "    }\n",
    "\n",
    "# # print(df.groupby('key2').agg(dict_mapping))\n",
    "\n",
    "# 多层列索引的选取\n",
    "data_grouped = df.groupby('key2').agg(dict_mapping)\n",
    "data_grouped['col1']\n",
    "# 修改列名 \n",
    "# data_grouped.columns = ['_'.join(col).strip() for col in data_grouped.columns.values]\n",
    "# data_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "69b65c22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key2</th>\n",
       "      <th>col1</th>\n",
       "      <th>col2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>x</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>37.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>y</td>\n",
       "      <td>14.428571</td>\n",
       "      <td>31.428571</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key2       col1       col2\n",
       "0    x  14.000000  37.000000\n",
       "1    y  14.428571  31.428571"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 结果去掉行索引\n",
    "# groupby(, as_index=False)\n",
    "df = pd.DataFrame(\n",
    "    {'col1': np.random.randint(10,20,10),\n",
    "    'col2' : np.random.randint(20,50,10),\n",
    "    'key1' : np.random.choice(['a','b'], 10, p = [0.3, 0.7]),\n",
    "    'key2' : np.random.choice(['x','y'], 10)}\n",
    ")\n",
    "\n",
    "dict_mapping = {\n",
    "    'col1': ['mean', 'max'],\n",
    "    'col2': ['sum'],\n",
    "    'key1' : ['first']\n",
    "    }\n",
    "\n",
    "grouped = df.groupby(['key2'],as_index = False).mean()\n",
    "grouped\n",
    "\n",
    "# 也可以使用reset_index()\n",
    "# as_index = False 效率更高\n",
    "\n",
    "grouped = df.groupby(['key2']).mean().reset_index()\n",
    "grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5bb9fb4",
   "metadata": {},
   "source": [
    "### transform()\n",
    "\n",
    "- 传入Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "05977af3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   col1  col2 key1 key2\n",
      "0    17    32    b    x\n",
      "1    11    41    b    x\n",
      "2    16    44    b    x\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>col1</th>\n",
       "      <th>col2</th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>col_ave_trans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17</td>\n",
       "      <td>32</td>\n",
       "      <td>b</td>\n",
       "      <td>x</td>\n",
       "      <td>13.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>b</td>\n",
       "      <td>x</td>\n",
       "      <td>13.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16</td>\n",
       "      <td>44</td>\n",
       "      <td>b</td>\n",
       "      <td>x</td>\n",
       "      <td>13.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   col1  col2 key1 key2  col_ave_trans\n",
       "0    17    32    b    x      13.666667\n",
       "1    11    41    b    x      13.666667\n",
       "2    16    44    b    x      13.666667"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(\n",
    "    {'col1': np.random.randint(10,20,10),\n",
    "    'col2' : np.random.randint(20,50,10),\n",
    "    'key1' : np.random.choice(['a','b'], 10, p = [0.3, 0.7]),\n",
    "    'key2' : np.random.choice(['x','y'], 10)}\n",
    ")\n",
    "print(df.head(3))\n",
    "\n",
    "# df.groupby('key1')['col1'].mean()\n",
    "\n",
    "# 需求，希望将'col1'的分组均值加在原df的每一行后面\n",
    "\n",
    "# result = df.groupby('key1')['col1'].mean().to_dict()\n",
    "# df['col_ave'] = df['key1'].map(result)\n",
    "# df.head(3)\n",
    "\n",
    "# 便捷操作，使用 transform，传入Series!!\n",
    "df['col_ave_trans'] =  df.groupby('key1')['col1'].transform('mean')\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "284756a3",
   "metadata": {},
   "source": [
    "### groupby + apply()\n",
    "\n",
    "- `apply`比agg和transform而言更加灵活，能够传入任意自定义的函数，实现复杂的数据操作。\n",
    "- 在groupby后使用apply，是将分组后的**子DataFrame**作为参数传入指定函数的，基本操作单位是DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "121cc969",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>econ_major</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91</td>\n",
       "      <td>189</td>\n",
       "      <td>89</td>\n",
       "      <td>False</td>\n",
       "      <td>female</td>\n",
       "      <td>22</td>\n",
       "      <td>freshman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>86</td>\n",
       "      <td>168</td>\n",
       "      <td>44</td>\n",
       "      <td>False</td>\n",
       "      <td>female</td>\n",
       "      <td>22</td>\n",
       "      <td>junior</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>47</td>\n",
       "      <td>165</td>\n",
       "      <td>66</td>\n",
       "      <td>False</td>\n",
       "      <td>male</td>\n",
       "      <td>22</td>\n",
       "      <td>senior</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>155</td>\n",
       "      <td>64</td>\n",
       "      <td>False</td>\n",
       "      <td>male</td>\n",
       "      <td>22</td>\n",
       "      <td>sophomore</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  height  weight  econ_major  gender  age      grade\n",
       "0  91     189      89       False  female   22   freshman\n",
       "1  86     168      44       False  female   22     junior\n",
       "2  47     165      66       False    male   22     senior\n",
       "3   1     155      64       False    male   22  sophomore"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "boolean=[True,False]\n",
    "gender=['female', 'male']\n",
    "grade = ['freshman', 'sophomore' , 'junior' , 'senior']\n",
    "df =pd.DataFrame({\n",
    "    'id' : np.arange(1,101,1),\n",
    "    'height' : np.random.randint(150,190,100),\n",
    "    'weight' : np.random.randint(40,90,100),\n",
    "    'econ_major' : np.random.choice(boolean, 100),\n",
    "    'gender' : np.random.choice(gender, 100),\n",
    "    'age' : np.random.randint(17,23,100),\n",
    "    \"grade\": np.random.choice(grade, 100)\n",
    "}\n",
    ")\n",
    "df.head()\n",
    "\n",
    "# 对grade分组，获得年龄最大学生的data\n",
    "def get_oldest(df):\n",
    "    df = df.sort_values('age', ascending = False)\n",
    "    return df.iloc[0,:]\n",
    "df.groupby(['grade'], as_index = False).apply(get_oldest)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca9032d1",
   "metadata": {},
   "source": [
    "```{note}\n",
    "- apply的运行效率比agg和transform慢。所以，groupby之后若能用agg和transform解决，优先使用这两个方法。\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69fe7293",
   "metadata": {},
   "source": [
    "## 透视表 (pivot_table)  & 交叉表 (crosstab)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e94b25f",
   "metadata": {},
   "source": [
    "### `pivot_table()`\n",
    "\n",
    "- A pivot table is **a data summarization tool**(数据汇总工具) frequently found in spreadsheet programs and other data analysis software. \n",
    "  - 透视表是一种可以对数据动态排布并且分类汇总的表格格式。同Excel中的数据透视表。\n",
    "- It aggregates a table of data by one or more keys, arranging the data in a rectangle(矩形) with some of the group keys along the rows and some along the columns.\n",
    "- 使用方法：\n",
    "- `df.pivot_table(data, values=None, index=None, columns=None,aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All')`\n",
    "  - 四个最重要的参数: values、index、columns、aggfunc\n",
    "  - values: Column name or names to aggregate; 默认聚合所有的数值列\n",
    "  - index: 层次字段，Column names or other group keys to group on the rows of the resulting pivot table.\n",
    "  - aggfunc： 设置对数据聚合时进行的函数操作，默认为 aggfunc='mean'计算均值。\n",
    "  - columns: 类似Index可以设置列层次字段，它不是一个必要参数，作为一种分割数据的可选方式。\n",
    "  - fill_value填充空值\n",
    "  - margins=True: 进行汇总"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "96396579",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age', 'SibSp',\n",
       "       'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "titanic = pd.read_csv('../../raw-data/titanic.csv')\n",
    "titanic.head()\n",
    "titanic.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "4e47ffdb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Age</th>\n",
       "      <th>Survived</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>max</th>\n",
       "      <th>min</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pclass</th>\n",
       "      <th>Sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>female</th>\n",
       "      <td>63.0</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.968085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>80.0</td>\n",
       "      <td>0.92</td>\n",
       "      <td>0.368852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>female</th>\n",
       "      <td>57.0</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.921053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>70.0</td>\n",
       "      <td>0.67</td>\n",
       "      <td>0.157407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>female</th>\n",
       "      <td>63.0</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>74.0</td>\n",
       "      <td>0.42</td>\n",
       "      <td>0.135447</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Age        Survived\n",
       "                max   min      mean\n",
       "Pclass Sex                         \n",
       "1      female  63.0  2.00  0.968085\n",
       "       male    80.0  0.92  0.368852\n",
       "2      female  57.0  2.00  0.921053\n",
       "       male    70.0  0.67  0.157407\n",
       "3      female  63.0  0.75  0.500000\n",
       "       male    74.0  0.42  0.135447"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# titanic.pivot_table(values = ['Survived','Age'], index = ['Pclass', 'Sex'])\n",
    "\n",
    "# titanic.pivot_table(values = ['Survived','Age'], index = ['Pclass'], columns = ['Sex'])\n",
    "\n",
    "# titanic.pivot_table(['Survived','Age'], index = ['Pclass', 'Sex'], aggfunc = len )\n",
    "# titanic.pivot_table(['Survived','Age'], index = ['Pclass', 'Sex'], aggfunc = 'count' )\n",
    "titanic.pivot_table(['Survived','Age'], index = ['Pclass', 'Sex'], aggfunc = {'Survived': 'mean', 'Age': ['max','min']} )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ceb92fab",
   "metadata": {},
   "source": [
    "### `crosstab()`\n",
    "\n",
    "- 是透视表的特例, aggfunc=count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "b122da10",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Survived</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pclass</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.370370</td>\n",
       "      <td>0.629630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.527174</td>\n",
       "      <td>0.472826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.757637</td>\n",
       "      <td>0.242363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>0.616162</td>\n",
       "      <td>0.383838</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Survived         0         1\n",
       "Pclass                      \n",
       "1         0.370370  0.629630\n",
       "2         0.527174  0.472826\n",
       "3         0.757637  0.242363\n",
       "All       0.616162  0.383838"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(titanic['Pclass'], titanic['Survived'], margins = True)\n",
    "pd.crosstab(titanic['Pclass'], titanic['Survived'], margins = True, normalize= 'index')\n",
    "# normalize: Normalize by dividing all values by the sum of values. \n",
    "# default is false\n",
    "# If passed 'all' or True, will normalize over all values.\n",
    "# If passed 'index' will normalize over each row.\n",
    "# If passed 'columns' will normalize over each column.\n",
    "# If margins is True, will also normalize margin values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "bcc25c5c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Survived</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pclass</th>\n",
       "      <th>Sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>female</th>\n",
       "      <td>0.031915</td>\n",
       "      <td>0.968085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>0.631148</td>\n",
       "      <td>0.368852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>female</th>\n",
       "      <td>0.078947</td>\n",
       "      <td>0.921053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>0.842593</td>\n",
       "      <td>0.157407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>female</th>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>0.864553</td>\n",
       "      <td>0.135447</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Survived              0         1\n",
       "Pclass Sex                       \n",
       "1      female  0.031915  0.968085\n",
       "       male    0.631148  0.368852\n",
       "2      female  0.078947  0.921053\n",
       "       male    0.842593  0.157407\n",
       "3      female  0.500000  0.500000\n",
       "       male    0.864553  0.135447"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# index or col 可以传入 a list of arrays\n",
    "pd.crosstab([titanic['Pclass'],titanic['Sex']], titanic['Survived'],normalize = 'index')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "539b8914",
   "metadata": {},
   "source": [
    "## 索引重塑 "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa20f1af",
   "metadata": {},
   "source": [
    "### 多层索引重塑\n",
    "\n",
    "- stack - 列拉长index\n",
    "​   - This \"rotates\" or pivots from the columns in the data to the rows\n",
    "\n",
    "- unstack\n",
    "​   - This pivots from the rows into the columns.\n",
    "​"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "a7afa1af",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>number</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>prov</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BJ</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SZ</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "number  2020  2021  2022\n",
       "prov                    \n",
       "BJ         0     1     2\n",
       "SZ         3     4     5"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(np.arange(6).reshape((2, 3)),\n",
    "    index=pd.Index(['BJ', 'SZ'], name='prov'),\n",
    "    columns=pd.Index(['2020', '2021', '2022'],\n",
    "    name='number'))\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "36bbd138",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>prov</th>\n",
       "      <th>BJ</th>\n",
       "      <th>SZ</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "prov    BJ  SZ\n",
       "number        \n",
       "2020     0   3\n",
       "2021     1   4\n",
       "2022     2   5"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# \"stack 将每一行, 叠成一个Series, 堆起来\"； 二维表转换为一维表\n",
    "result = df.stack()\n",
    "\n",
    "result\n",
    "\n",
    "# \"unstack 将叠起来的Series, 变回DF\"\n",
    "result.unstack()\n",
    "\n",
    "# By default the innermost level is unstacked(same with stack). You can unstack a different level by passing a level number or name.\n",
    "\n",
    "result.unstack(level = 0) \n",
    "# the same as \n",
    "result.unstack(level='prov')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "d33d7da2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>side</th>\n",
       "      <th colspan=\"2\" halign=\"left\">left</th>\n",
       "      <th colspan=\"2\" halign=\"left\">right</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>prov</th>\n",
       "      <th>BJ</th>\n",
       "      <th>SZ</th>\n",
       "      <th>BJ</th>\n",
       "      <th>SZ</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "side   left    right    \n",
       "prov     BJ SZ    BJ  SZ\n",
       "number                  \n",
       "2020      0  3     5   8\n",
       "2021      1  4     6   9\n",
       "2022      2  5     7  10"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'left': result, 'right': result + 5},\n",
    "columns=pd.Index(['left', 'right'], name='side'))\n",
    "df\n",
    "\n",
    "df.unstack(\"prov\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "83cdec07",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prov</th>\n",
       "      <th>BJ</th>\n",
       "      <th>SZ</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number</th>\n",
       "      <th>side</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2020</th>\n",
       "      <th>left</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>right</th>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2021</th>\n",
       "      <th>left</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>right</th>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2022</th>\n",
       "      <th>left</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>right</th>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "prov          BJ  SZ\n",
       "number side         \n",
       "2020   left    0   3\n",
       "       right   5   8\n",
       "2021   left    1   4\n",
       "       right   6   9\n",
       "2022   left    2   5\n",
       "       right   7  10"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# When calling stack, we can indicate the name of the axis to stack:\n",
    "df.unstack('prov').stack('side')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2e06caa",
   "metadata": {},
   "source": [
    "- `pd.pivot()` : Return reshaped DataFrame organized by given index / column values.\n",
    "- `pd.melt()`: Unpivot a DataFrame from wide to long format, optionally leaving identifiers set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "f1f8f30b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number  2020  2021  2022\n",
      "prov                    \n",
      "BJ         0     1     2\n",
      "SZ         3     4     5\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prov</th>\n",
       "      <th>number</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BJ</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SZ</td>\n",
       "      <td>2020</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BJ</td>\n",
       "      <td>2021</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SZ</td>\n",
       "      <td>2021</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BJ</td>\n",
       "      <td>2022</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SZ</td>\n",
       "      <td>2022</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prov number  value\n",
       "0   BJ   2020      0\n",
       "1   SZ   2020      3\n",
       "2   BJ   2021      1\n",
       "3   SZ   2021      4\n",
       "4   BJ   2022      2\n",
       "5   SZ   2022      5"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(np.arange(6).reshape((2, 3)),\n",
    "    index=pd.Index(['BJ', 'SZ'], name='prov'),\n",
    "    columns=pd.Index(['2020', '2021', '2022'],\n",
    "    name='number'))\n",
    "\n",
    "a = df.stack()\n",
    "a = pd.DataFrame(a).reset_index()\n",
    "a\n",
    "b = a.pivot(index = 'prov', columns='number', values = 0)\n",
    "print(b)\n",
    "b.reset_index(inplace = True)\n",
    "b.melt('prov')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
